package dbAccess;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.sql.*;

public class Staff {

	private final static String defaultStr = "unknown";
	private int staffId;
	private String EmailAddress;
	private int DocId;
	private int HasAccess;
	private int SIN;
	private String FirstName;
	private String LastName;
	private String PhoneNum;
	private String HomeAddress;
	private String Gender;
	private java.sql.Date DateOfBirth;
	private String password;

	private String statusMsg;
	private String creator;
	
	public Staff(String creatorIn)
	{
		staffId = -1;
		
		EmailAddress=defaultStr;
		SIN=-1;
		DocId=-1;
		HasAccess=0; //default
		FirstName=defaultStr;
		LastName=defaultStr;
		PhoneNum=defaultStr;
		HomeAddress=defaultStr;
		Gender=defaultStr;
		DateOfBirth = java.sql.Date.valueOf("0000-00-00");
		password=defaultStr;
		
		statusMsg="";
		creator = creatorIn;
	}
	
	//creates new staff or updates an existing one
	public Boolean registerStaff(String action) 
	{
		if(action.equals("update"))
		{
			int staffId = this.getStaffId();
			String updateQuery = "update tbl_staff set IsDeleted = '1' where StaffId = " + staffId ;
			DbAccess access = new DbAccess();
			access.ExecuteUpdate(access.connect(), updateQuery);
			Login.Log(creator, updateQuery);
		}
		
		this.setStatusMsg("");
		//check if SIN or email address already exists in tbl_person
		DbAccess access = new DbAccess();
		String query = "select SIN, EmailAddress from tbl_staff where (SIN = " + SIN + " or EmailAddress = '" + EmailAddress + "'";
		query += ") and IsDeleted = '0' ";
		
		try 
		{
		ResultSet rs = access.ExecuteSelect(access.connect(), query);
		if (rs.next())
		{
			this.setStatusMsg("Staff member already exists.");
			return false;
		}
		}
		catch (SQLException e)
		{
			this.setStatusMsg("Duplication check error occurred: " + e.getMessage());
			return false;
		}
			
		//insert into tbl_patient
		query = "insert into tbl_staff ";
		query += "(EmailAddress, DocId, HasAccess, SIN, FirstName, LastName, Phone, HomeAddress, Gender, ";
		query += "DateOfBirth, Password, IsDeleted) ";
		query += "values ('" + EmailAddress + "'," + DocId + ",'" + HasAccess + "', " + SIN + ", '";
		query += FirstName + "','" + LastName + "','" + PhoneNum + "','" + HomeAddress + "','" + Gender + "','";
		query += DateOfBirth + "','" + password + "',0)";
		
		if (!(access.ExecuteUpdate(access.connect(), query)))
		{
			this.setStatusMsg("Could not update tbl_staff with new staff information.");
			return false;
		}
		else
		{
			Login.Log(creator, query);
		}
		
		int oldStaffId = this.getStaffId();
		//store staff ID field
		if (!(evalStaffId()))
		{
			return false;
		}
		
		if(action.equals("update"))
		{
			//must update other references to old staff id in tbl_appointment, tbl_staff_patient_access
			int staffId = this.getStaffId();
			String updateApptQuery = "update tbl_appointment set tbl_appointment.StaffId = " + staffId + " where " ;
			updateApptQuery += "tbl_appointment.StaffId = " + oldStaffId;
			
			String updateStaffAccessQuery = "update tbl_staff_patient_access set tbl_staff_patient_access.StaffId = " + staffId + " where " ;
			updateStaffAccessQuery += "tbl_staff_patient_access.StaffId = " + oldStaffId;
		
			DbAccess access2 = new DbAccess();
			
			access2.ExecuteUpdate(access2.connect(), updateApptQuery);
			Login.Log(creator, updateApptQuery);
			
			access2.ExecuteUpdate(access2.connect(), updateStaffAccessQuery);
			Login.Log(creator, updateStaffAccessQuery);
			
			this.setStatusMsg(this.getFirstName() + " " + this.getLastName() + " updated with new ID: " + this.getStaffId());
			return true;
		}
		this.setStatusMsg("New staff created with ID: " + this.getStaffId());
		return true;
	}
	
	private Boolean evalStaffId()
	{
		DbAccess access = new DbAccess();
		String query = "select tbl_staff.StaffId from tbl_staff where tbl_staff.EmailAddress = '" + this.EmailAddress + "' and ";
		query += "tbl_staff.IsDeleted = 0";
		
		try
		{
			ResultSet rs = access.ExecuteSelect(access.connect(), query);
			rs.next();
			this.staffId = rs.getInt("tbl_staff.StaffId");
			return true;
		}
		catch (SQLException e)
		{
			return false;
		}
	}
	
	public Boolean provideStaffPatientAccess(int StaffId, int DocId)
	{
		DbAccess access = new DbAccess(); 
		DbAccess access2 = new DbAccess();
		String query =  "SELECT PatientId FROM tbl_patient WHERE DocId='" + DocId + "' AND IsDeleted=0";
		ResultSet rs = access.ExecuteSelect(access.connect(), query);
		try {
			while(rs.next())
			{
				Date now = new Date();
				Calendar calendar = Calendar.getInstance();
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				
				
				String myQuery = "INSERT INTO tbl_staff_patient_access(DocId, PatientId, StaffId, TimeStamp, Valid)" +
								 "VALUES('" + DocId + "', '" + rs.getString("PatientId") + "', '" + StaffId + 
								 "', '" + dateFormat.format(calendar.getTime()) + "', '1')";
				if(!access2.ExecuteUpdate(access.connect(), myQuery))
				{
					this.setStatusMsg("This staff member already has access for the patient");
					return false; 
				}
				else
				{
					Login.Log(creator, myQuery);
				}
			}
		} catch (SQLException e) {
			this.setStatusMsg("Query failed.");
			return false;
		}
		return true;
	}

	public Boolean removeStaffPatientAccess(int StaffId)
    {
        DbAccess access = new DbAccess();
        DbAccess access2 = new DbAccess();
        String docId = access.ExecuteScalar(access.connect(), "SELECT DocId FROM tbl_staff WHERE StaffId='" + StaffId + "'");
        String query =  "UPDATE tbl_staff_patient_access SET Valid='0' WHERE DocId='" + docId + "' AND StaffId='" + StaffId + "'";
        if(!access2.ExecuteUpdate(access.connect(), query))
        {
            this.setStatusMsg("Access couldn't be removed.");
            return false;
        }
        else
        {
        	Login.Log(creator, query);
        }
        return true;
    }


public Boolean removeStaff(int StaffId)
    {
        DbAccess access = new DbAccess();
        //remove access tables
        String query = "DELETE FROM tbl_staff_patient_access WHERE StaffId='" + StaffId + "'";
        if(!access.ExecuteUpdate(access.connect(), query))
        {
            this.setStatusMsg("failed to update staff/patient access records");
            return false;
        }
        else
        {
        	Login.Log(creator, query);
        }
       
        //set tbl_staff.IsDeleted
        query = "UPDATE tbl_staff SET IsDeleted=1 WHERE StaffId=" + StaffId;
        if(!access.ExecuteUpdate(access.connect(), query))
        {
            this.setStatusMsg("failed to update staff records");
            return false;
        }
        else
        {
        	Login.Log(creator, query);
        }

        //Set tbl_appointment.staffId
        Date now = new Date();
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
        query = "INSERT INTO tbl_appointment(DocId,PatientId,Date,StartTime,EndTime,Building,Room,StaffId,IsDeleted)" +
                "SELECT DocId, PatientId, Date, StartTime, EntTime, Building, Room, '0', '0' WHERE StaffId='" +
                StaffId + "' AND Date > '" + dateFormat.format(calendar.getTime()) + "'";
        if(!access.ExecuteUpdate(access.connect(), query))
        {
            this.setStatusMsg("failed to update appointment records");
            return false;
        }
        else
        {
        	Login.Log(creator, query);
        }
       
        //Set tbl_appointment.IsDeleted
        query = "UPDATE tbl_appointment SET IsDeleted=1 WHERE StaffId=" + StaffId;
        if(!access.ExecuteUpdate(access.connect(), query))
        {
            this.setStatusMsg("failed to update appointment records");
            return false;
        }
        else
        {
        	Login.Log(creator, query);
        }
               
        this.setStatusMsg("Staff has been deleted.");
        return true;
    }
	
	public int getStaffId() {
		return staffId;
	}

	public void setStaffId(int staffId) {
		this.staffId = staffId;
	}

	public int getSIN() {
		return SIN;
	}

	public void setSIN(int sin) {
		SIN = sin;
	}

	public String getFirstName() {
		return FirstName;
	}

	public void setFirstName(String firstName) {
		FirstName = firstName;
	}

	public String getLastName() {
		return LastName;
	}

	public void setLastName(String lastName) {
		LastName = lastName;
	}

	public String getEmailAddress() {
		return EmailAddress;
	}

	public void setEmailAddress(String emailAddress) {
		EmailAddress = emailAddress;
	}

	public String getPhoneNum() {
		return PhoneNum;
	}

	public void setPhoneNum(String phoneNum) {
		PhoneNum = phoneNum;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public int getDocId() {
		return DocId;
	}

	public void setDocId(int docId) {
		DocId = docId;
	}

	public String getStatusMsg() {
		return statusMsg;
	}

	public void setStatusMsg(String statusMsg) {
		this.statusMsg = statusMsg;
	}
	public int getHasAccess() {
		return HasAccess;
	}

	public void setHasAccess(int hasAccess) {
		HasAccess = hasAccess;
	}

	public String getHomeAddress() {
		return HomeAddress;
	}

	public void setHomeAddress(String homeAddress) {
		HomeAddress = homeAddress;
	}

	public String getGender() {
		return Gender;
	}

	public void setGender(String gender) {
		Gender = gender;
	}

	public java.sql.Date getDateOfBirth() {
		return DateOfBirth;
	}

	public void setDateOfBirth(java.sql.Date dateOfBirth) {
		DateOfBirth = dateOfBirth;
	}
	
	
}
